package main.reports;

import main.SQL.SQLTaskRsrc;
import main.Trees.ProjectNode;
import main.Trees.SmetNode;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;

import javax.swing.*;
import javax.swing.filechooser.FileFilter;
import javax.swing.filechooser.FileNameExtensionFilter;
import java.awt.*;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.util.ArrayList;
import java.util.HashMap;

public class XLSReportTaskSmet extends Thread{
    private HashMap<String, CellStyle> styles;
    private ProjectNode root;
    private String pathXLSFile;
    HSSFWorkbook wb;
    HSSFSheet sheet;
    int rownum;
    int childCount;
    int sheetCount;

    public XLSReportTaskSmet(ProjectNode root) {
        this.root = root;
    }

    public void run() {
        chooseFile();
    }

    private void chooseFile() {
        JFileChooser fileChooser = new JFileChooser();
        File fle = new File("Отчет по привязке расценок к работам графика "+root.getShortName().replace("/", "-").replace("\\", "-")+".xls");
        fileChooser.setSelectedFile(fle);

        FileFilter filter = new FileNameExtensionFilter("Файлы Excel - XLS", "xls");
        fileChooser.setFileFilter(filter);
        String homeDir = new File("").getAbsolutePath();
        fileChooser.setCurrentDirectory(new File(homeDir));
        if (fileChooser.showSaveDialog(null) == JFileChooser.APPROVE_OPTION) {
            pathXLSFile = fileChooser.getSelectedFile().getAbsolutePath();
            if(!pathXLSFile.toLowerCase().endsWith(".xls"))
            {
                pathXLSFile = pathXLSFile + ".xls";
            }
            generateXLS();
        }
    }

    private void countAllChilds(ProjectNode node) {
        if (node.getChildren() == null) return;
        this.childCount += node.getChildCount();
        for (ProjectNode child : node.getChildren()) {
            countAllChilds (child);
        }
    }

    private void getRow (ProjectNode curNode) {

        if (curNode.getName().contains("[WBS1]")) {

            childCount = 0;
            countAllChilds(curNode);
            //System.out.println ("WBS 1 - "+ curNode.getName()+", childCount = " + childCount +", total rows = " + (Integer.valueOf(rownum + childCount)));
            if (rownum + childCount > 65535) {
                //region Устанавливаем ширины столбцов в Excel
                sheet.setColumnWidth(0, 256*35);
                sheet.setColumnWidth(1, 256*9);
                sheet.setColumnWidth(2, 256*75);
                sheet.setColumnWidth(3, 256*17);
                sheet.setColumnWidth(4, 256*12);
                sheet.setColumnWidth(5, 256*12);
                sheet.setColumnWidth(6, 256*12);
                sheet.setColumnWidth(7, 256*17);
                sheet.setColumnWidth(8, 256*14);
                sheet.setColumnWidth(9, 256*11);
                sheet.setColumnWidth(10, 256*10);
                //endregion

                sheetCount++ ;
                sheet = wb.createSheet("LinkList" + sheetCount);
                rownum = 0;
                sheet.setRowSumsBelow(false);
                addTitle();
            }
        }

        Row row = sheet.createRow(rownum++);
//        row.setHeightInPoints(15.00f);
        Cell cell;


        for (int col = 0; col < 13; col++) {
            cell = row.createCell(col);
            switch (col) {
                case 0:
                    cell.setCellValue(curNode.isSmString() ? curNode.getSmrSmNum() : "");
                    break;
                case 1:
                    cell.setCellValue(curNode.isSmString() ? curNode.getName() : curNode.getShortName());
                    break;
                case 2:
                    cell.setCellValue(curNode.isSmString() ? curNode.getSmrName() : curNode.getName());
                    break;
                case 3:
                    if (curNode.isSmString()) {
                        cell.setCellValue(curNode.getSmrUnit());
                    } else {
                        cell.setCellValue(curNode.getTaskCodeValue());
                    }
                    break;
                case 4:
                    if (curNode.isSmString()) cell.setCellValue(curNode.getSmrSmrVol());
                    break;
                case 5:
                    if (curNode.isSmString()) cell.setCellValue(curNode.getSmrLinkVol());
                    break;
                case 6:
                    if (curNode.isSmString()) {
                        if (curNode.getIsPV()) {
                            cell.setCellValue(curNode.getPV());
                        }
                        break;
                    } else {
                        for (SQLTaskRsrc cur : curNode.getRsrc()) {
                            if (cur.getRsrcShortName().contains("PV")) cell.setCellValue(cur.getPlannedUnits());
                        }
                        break;
                    }
                case 7:
                    if (curNode.isSmString() && curNode.getIsPV()) {
                        String unit = curNode.getSmrUnit();
                        if (curNode.getSmrUnit() != null && curNode.getSmrUnit().length() > 3) {
                            String sup = curNode.getSmrUnit().substring(0,4);
                            if (sup.contains("1000")) {
                                unit = curNode.getSmrUnit().replace("1000", "");
                            } else if (sup.contains("100")) {
                                unit = curNode.getSmrUnit().replace("100", "");
                            } else if (sup.contains("10")) {
                                unit = curNode.getSmrUnit().replace("10", "");
                            }
                        }
                        cell.setCellValue(unit);
                    }
                    break;
                case 8:
                    if (curNode.isSmString()) {
                        cell.setCellValue(curNode.getSmrMrVol());
                    } else {
                        for (SQLTaskRsrc cur : curNode.getRsrc()) {
                            if (cur.getRsrcShortName().equals("MR")) cell.setCellValue(cur.getPlannedUnits());
                        }
                    }
                    break;
                case 9:
                    if (curNode.isSmString()) {
                        cell.setCellValue(curNode.getSmrTVol());
                    } else {
                        for (SQLTaskRsrc cur : curNode.getRsrc()) {
                            if (cur.getRsrcShortName().equals("T") || cur.getRsrcShortName().contains("КИПА") || cur.getRsrcShortName().contains("ЭС") || cur.getRsrcShortName().contains("АЗО") || cur.getRsrcShortName().contains("ОБС") || cur.getRsrcShortName().contains("МК") || cur.getRsrcShortName().contains("ТХМ")  || cur.getRsrcShortName().contains("МЕХ") || cur.getRsrcShortName().contains("ТИ")) cell.setCellValue(cur.getPlannedUnits());
                        }
                    }
                    break;
                case 10:
                    if (curNode.isSmString()) {
                        cell.setCellValue(curNode.getSmrMechVol());
                    } else {
                        for (SQLTaskRsrc cur : curNode.getRsrc()) {
                            if (cur.getRsrcShortName().equals("MECH")) cell.setCellValue(cur.getPlannedUnits());
                        }
                    }
                    break;
                case 11:
                    cell.setCellValue(curNode.isSmString() ? "" : curNode.getSector());
                    break;
                case 12:
                    cell.setCellValue(curNode.isSmString() ? "" : curNode.getBlock());
                    break;

            }

            if (curNode.isSmString()) {
                setSmStringStyle(curNode, cell, col);
            } else {
                setStringStyle(curNode, cell, col);
            }
        }

        if (curNode.getChildren() == null) return;

        int groupStart = rownum;

        for (ProjectNode child : curNode.getChildren()) {
            getRow(child);
        }

        sheet.groupRow(groupStart, rownum);

    }

    private void addTitle () {
        //region Первая строка заголовка
        Row row = sheet.createRow(rownum++);
        row.setHeightInPoints(15.00f);
        Cell cell;

        cell = row.createCell(0);
        cell.setCellStyle(styles.get("topTitle_bottom_border"));

        cell = row.createCell(1);
        cell.setCellValue("ID");
        cell.setCellStyle(styles.get("topTitle_bottom_right_border"));

        cell = row.createCell(2);
        cell.setCellValue("Наименование работы / раздела");
        cell.setCellStyle(styles.get("topTitle_bottom_border"));

        cell = row.createCell(3);
        cell.setCellStyle(styles.get("topTitle_bottom_border"));

        cell = row.createCell(4);
        cell.setCellStyle(styles.get("topTitle_bottom_border"));

        cell = row.createCell(5);
        cell.setCellStyle(styles.get("topTitle_bottom_border"));

        cell = row.createCell(6);
        cell.setCellStyle(styles.get("topTitle_bottom_border"));

        cell = row.createCell(7);
        cell.setCellStyle(styles.get("topTitle_bottom_border"));

        cell = row.createCell(8);
        cell.setCellStyle(styles.get("topTitle_bottom_border"));

        cell = row.createCell(9);
        cell.setCellStyle(styles.get("topTitle_bottom_border"));

        cell = row.createCell(10);
        cell.setCellStyle(styles.get("topTitle_bottom_border"));

        cell = row.createCell(11);
        cell.setCellStyle(styles.get("topTitle_bottom_border"));

        cell = row.createCell(12);
        cell.setCellStyle(styles.get("topTitle_bottom_right_border"));
        //endregion

        //region Вторая строка заголовка
        row = sheet.createRow(rownum++);

        cell = row.createCell(0);
        cell.setCellValue("№ сметы");
        cell.setCellStyle(styles.get("topTitle_borders"));

        cell = row.createCell(1);
        cell.setCellValue("№ сметной строки");
        cell.setCellStyle(styles.get("topTitle_borders"));

        cell = row.createCell(2);
        cell.setCellValue("Наименование сметной строки");
        cell.setCellStyle(styles.get("topTitle_borders"));

        cell = row.createCell(3);
        cell.setCellValue("Ед. изм.");
        cell.setCellStyle(styles.get("topTitle_borders"));

        cell = row.createCell(4);
        cell.setCellValue("Кол-во по сметной строке");
        cell.setCellStyle(styles.get("topTitle_borders"));

        cell = row.createCell(5);
        cell.setCellValue("Кол-во по сметной строке, отнесенное к работе");
        cell.setCellStyle(styles.get("topTitle_borders"));

        cell = row.createCell(6);
        cell.setCellValue("Физ. объем, отнесенный к работе");
        cell.setCellStyle(styles.get("topTitle_borders"));

        cell = row.createCell(7);
        cell.setCellValue("Ед. изм. Физ. объема, отнесенного к работе");
        cell.setCellStyle(styles.get("topTitle_borders"));

        cell = row.createCell(8);
        cell.setCellValue("Прямые затраты");
        cell.setCellStyle(styles.get("topTitle_borders"));

        cell = row.createCell(9);
        cell.setCellValue("Количество трудозатрат");
        cell.setCellStyle(styles.get("topTitle_borders"));

        cell = row.createCell(10);
        cell.setCellValue("Количество машино-часов");
        cell.setCellStyle(styles.get("topTitle_borders"));

        cell = row.createCell(11);
        cell.setCellValue("Сектор");
        cell.setCellStyle(styles.get("topTitle_borders"));

        cell = row.createCell(12);
        cell.setCellValue("Блок");
        cell.setCellStyle(styles.get("topTitle_borders"));

        //endregion
    }

    private void setStringStyle(ProjectNode node, Cell cell, int col) {
        if (node.getName().contains("[ROOT]")) {
            cell.setCellStyle(styles.get(col == 2 ? "root_left" : "root"));
            cell.setCellValue(cell.getStringCellValue().replace("[ROOT]", ""));
        } else if (node.getName().contains("[WBS0]")) {
            cell.setCellStyle(styles.get(col == 2 ? "wbs0_left" : "wbs0"));
            cell.setCellValue(cell.getStringCellValue().replace("[WBS0]", ""));
        } else if (node.getName().contains("[WBS1]")) {
            cell.setCellStyle(styles.get(col == 2 ? "wbs1_left" : "wbs1"));
            cell.setCellValue(cell.getStringCellValue().replace("[WBS1]", ""));
        } else if (node.getName().contains("[WBS2]")) {
            cell.setCellStyle(styles.get(col == 2 ? "wbs2_left" : "wbs2"));
            cell.setCellValue(cell.getStringCellValue().replace("[WBS2]", ""));
        } else if (node.getName().contains("[WBS3]")) {
            cell.setCellStyle(styles.get(col == 2 ? "wbs3_left" : "wbs3"));
            cell.setCellValue(cell.getStringCellValue().replace("[WBS3]", ""));
        } else if (node.getName().contains("[WBS4]")) {
            cell.setCellStyle(styles.get(col == 2 ? "wbs4_left" : "wbs4"));
            cell.setCellValue(cell.getStringCellValue().replace("[WBS4]", ""));
        } else if (node.getName().contains("[WBS5]")) {
            cell.setCellStyle(styles.get(col == 2 ? "wbs5_left" : "wbs5"));
            cell.setCellValue(cell.getStringCellValue().replace("[WBS5]", ""));
        } else {
            if (col == 1) {
                cell.setCellStyle(styles.get("task_center"));
            } else if (col == 2 || col == 12 || col == 11) {
                cell.setCellStyle(styles.get("task_left"));
            } else {
                cell.setCellStyle(styles.get("task_double"));
            }

        }

    }

    private void setSmStringStyle(ProjectNode node, Cell cell, int col) {
        if (col == 0) {
            cell.setCellStyle(styles.get("sm_left_grey"));
        } else if (col == 1) {
            cell.setCellStyle(styles.get("sm_center"));
        } else if (col == 2 || col ==3 || col == 7) {
            cell.setCellStyle(styles.get("sm_left"));
        } else {
            cell.setCellStyle(styles.get("sm_double"));
        }

    }

    private void createStyles(){
        styles = new HashMap<>();

        CellStyle style;

        HSSFPalette palette = wb.getCustomPalette();
        palette.setColorAtIndex((short)57, (byte)217, (byte)217, (byte)217);

        HSSFFont mainFont = wb.createFont();
        mainFont.setFontHeightInPoints((short)10);
        mainFont.setFontName("Calibri");

        HSSFFont boldFont = wb.createFont();
        boldFont.setFontHeightInPoints((short)10);
        boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        boldFont.setFontName("Calibri");

        HSSFFont whiteFont = wb.createFont();
        whiteFont.setFontHeightInPoints((short)10);
        whiteFont.setColor(IndexedColors.WHITE.getIndex());
        whiteFont.setFontName("Calibri");

        HSSFFont whiteBoldFont = wb.createFont();
        whiteBoldFont.setFontHeightInPoints((short) 10);
        whiteBoldFont.setColor(IndexedColors.WHITE.getIndex());
        whiteBoldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        whiteBoldFont.setFontName("Calibri");

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(57).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setFont(boldFont);
        style.setWrapText(true);
        styles.put("topTitle_bottom_border", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(57).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setFont(boldFont);
        style.setWrapText(true);
        styles.put("topTitle_bottom_right_border", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(57).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.BLACK.index);

        style.setFont(boldFont);
        style.setWrapText(true);
        styles.put("topTitle_borders", style);

        palette.setColorAtIndex((short) 58, (byte) 22, (byte) 54, (byte) 92);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(58).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(whiteBoldFont);
        style.setWrapText(true);
        styles.put("root", style);

        palette.setColorAtIndex((short) 59, (byte) 83, (byte) 141, (byte) 213);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(59).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(whiteFont);
        style.setWrapText(true);
        styles.put("wbs0", style);

        palette.setColorAtIndex((short) 63, (byte) 144, (byte) 162, (byte) 200);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(63).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setWrapText(true);
        styles.put("wbs1", style);

        palette.setColorAtIndex((short) 60, (byte) 167, (byte) 168, (byte) 181);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(60).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setWrapText(true);
        styles.put("wbs2", style);

        palette.setColorAtIndex((short) 61, (byte) 189, (byte) 174, (byte) 163);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(61).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setWrapText(true);
        styles.put("wbs3", style);

        palette.setColorAtIndex((short) 62, (byte) 211, (byte) 181, (byte) 144);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(62).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setWrapText(true);
        styles.put("wbs4", style);

        palette.setColorAtIndex((short) 64, (byte) 232, (byte) 183, (byte) 124);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(64).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setWrapText(true);
        styles.put("wbs5", style);

        palette.setColorAtIndex((short) 58, (byte) 22, (byte) 54, (byte) 92);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(58).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(whiteBoldFont);
        styles.put("root_left", style);

        palette.setColorAtIndex((short) 59, (byte) 83, (byte) 141, (byte) 213);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(59).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(whiteFont);
        styles.put("wbs0_left", style);

        palette.setColorAtIndex((short) 63, (byte) 144, (byte) 162, (byte) 200);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(63).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        styles.put("wbs1_left", style);

        palette.setColorAtIndex((short) 60, (byte) 167, (byte) 168, (byte) 181);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(60).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        styles.put("wbs2_left", style);

        palette.setColorAtIndex((short) 61, (byte) 189, (byte) 174, (byte) 163);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(61).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
          styles.put("wbs3_left", style);

        palette.setColorAtIndex((short) 62, (byte) 211, (byte) 181, (byte) 144);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(62).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        styles.put("wbs4_left", style);

        palette.setColorAtIndex((short) 64, (byte) 232, (byte) 183, (byte) 124);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(64).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        styles.put("wbs5_left", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setFont(boldFont);
        style.setWrapText(true);
        styles.put("task_left", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setFont(boldFont);
        style.setWrapText(true);
        styles.put("task_center", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);

        HSSFCreationHelper creationHelper = wb.getCreationHelper();
        style.setDataFormat(creationHelper.createDataFormat().getFormat("#,##0.00"));
        style.setFont(boldFont);
        style.setWrapText(true);
        styles.put("task_double", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setDataFormat(creationHelper.createDataFormat().getFormat("#,##0.00"));
        style.setFont(mainFont);
        style.setWrapText(true);
        styles.put("sm_double", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setFont(mainFont);
        style.setWrapText(true);
        styles.put("sm_left", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setFont(mainFont);
        style.setWrapText(true);
        styles.put("sm_center", style);

        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.GREY_40_PERCENT.index);

        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.GREY_40_PERCENT.index);
        style.setFillForegroundColor(palette.getColor(57).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        style.setFont(mainFont);
        style.setWrapText(true);
        styles.put("sm_left_grey", style);

    }

    public void generateXLS() {

        BusyDialog busyDlg = new BusyDialog("Сохранение отчета", "<html>Подождите, пожалуйста.<br> Идет сохранение файла отчета...</html>");
        busyDlg.start();

        wb = new HSSFWorkbook();
        sheetCount = 1;
        sheet = wb.createSheet("LinkList" + sheetCount);
        rownum = 0;

        sheet.setRowSumsBelow(false);

        createStyles();
        addTitle();

        getRow(root);

        //region Устанавливаем ширины столбцов в Excel
        sheet.setColumnWidth(0, 256*35);
        sheet.setColumnWidth(1, 256*9);
        sheet.setColumnWidth(2, 256*75);
        sheet.setColumnWidth(3, 256*17);
        sheet.setColumnWidth(4, 256*12);
        sheet.setColumnWidth(5, 256*12);
        sheet.setColumnWidth(6, 256*12);
        sheet.setColumnWidth(7, 256*17);
        sheet.setColumnWidth(8, 256*14);
        sheet.setColumnWidth(9, 256*11);
        sheet.setColumnWidth(10, 256*10);
        sheet.setColumnWidth(11, 256*11);
        sheet.setColumnWidth(12, 256*17);
        //endregion

        Boolean isOk = true;

        FileOutputStream out;
        try {
            out = new FileOutputStream(new File(pathXLSFile));
            wb.write(out);
            out.close();
        } catch (IOException e) {
            busyDlg.setDescription("<html>Нет возможности сохранить отчет по адресу:<br>"+pathXLSFile+"<br> <br>Возможно, файл открыт в другой программе<br>Для завершения нажмите \"Готово\"</html>");
            busyDlg.stopBusy();
            isOk = false;
        }
        if (isOk) {
            busyDlg.setDescription("<html>Отчет сохранен по адресу:<br>"+pathXLSFile+"<br> <br>Для завершения нажмите \"Готово\"</html>");
            busyDlg.stopBusy();
        }
    }
}
